CREATE VIEW [dbo].[vDonations]
   AS
    SELECT    
        MAX(Activity.ID)                        ID,
        Activity.ORIGINATING_TRANS_NUM            OriginalTransaction,
        MAX(P.INVOICE_REFERENCE_NUM) AS         InvoiceRefNum,
        MAX(Activity.SOURCE_SYSTEM)             SourceSystem,
        MAX(Activity.TRANSACTION_DATE)          TransactionDate,
        MAX(Activity.EFFECTIVE_DATE)            DateReceived,
        (SUM(C.AMOUNT) * -1)  AS                Amount,
        MAX(Activity.SOLICITOR_ID)              SolicitorID,
        (CASE WHEN MAX(Activity.ACTIVITY_TYPE) = 'GIFT'
                 THEN MAX(P.CHECK_NUMBER)   
        ELSE   '' END)                            CheckNumber,
           (CASE WHEN MAX(Activity.ACTIVITY_TYPE) = 'GIFT' THEN
                (CASE MAX(Cash_Accounts.ACCOUNT_TYPE)
                    WHEN 1 THEN 'Credit Card'
                    WHEN 2 THEN 'In Kind'
                    WHEN 3 THEN 'Debit Card'
                    ELSE  'Cash'
                END)
                ELSE  '' END) AS PaymentType,
        CONVERT(int, SUBSTRING(CONVERT(char(6),MAX( P.FISCAL_PERIOD)), 5, 2)) AS FiscalMonth,   
        CONVERT(int,substring(CONVERT(char(6),MAX(P.FISCAL_PERIOD)),1,4)) AS FiscalYear,
        (CASE
            WHEN MAX(Activity.ACTIVITY_TYPE) = 'GIFT'
            THEN 'Gift'
            ELSE  'Pledge' END) AS         GiftType,
        MAX(P.MATCH_GIFT_TRANS_NUM)             MatchingTransaction,
        MAX(P.IS_MATCH_GIFT)                    IsMatchingGift,
        MAX(P.MEM_TRIB_ID)                      MemorialID,
        MAX(Activity.ACTION_CODES)              ListAs,
        MAX(Activity.UF_4)                      RequestNumber,
        MAX(P.MEM_TRIB_NAME_TEXT)               MemorialNameText  
    FROM Trans P
        INNER JOIN (SELECT DISTINCT TransactionNumber FROM TransWatch) tw ON
            tw.TransactionNumber = P.TRANS_NUMBER
        INNER JOIN Activity ON
            P.ACTIVITY_SEQN = Activity.SEQN
        LEFT OUTER JOIN Cash_Accounts ON
            P.CHECK_NUMBER = Cash_Accounts.CASH_ACCOUNT_CODE
        INNER JOIN Invoice ON
            Invoice.REFERENCE_NUM = P.INVOICE_REFERENCE_NUM
        INNER JOIN Trans C ON
            Invoice.REFERENCE_NUM = C.INVOICE_REFERENCE_NUM
    WHERE        
        C.ST_ID=Activity.ID AND
        C.TRANSACTION_TYPE = 'DIST' AND
        P.JOURNAL_TYPE = 'IN' AND
        C.IS_FR_ITEM = 1 AND
        P.TRANSACTION_TYPE = 'DIST' AND
        P.PRODUCT_CODE = C.PRODUCT_CODE AND
        (Invoice.SOURCE_SYSTEM = 'FR' OR (Invoice.SOURCE_SYSTEM = 'DUES' AND P.INVOICE_LINE_NUM = C.INVOICE_LINE_NUM))
    GROUP BY Activity.ID, Activity.ORIGINATING_TRANS_NUM
UNION
    SELECT
        MAX(Activity.ID)                        ID,
        Invoice.ORIGINATING_TRANS_NUM AS        OriginalTransaction,
        MAX(Trans.INVOICE_REFERENCE_NUM)        InvoiceRefNum,
        MAX(Activity.SOURCE_SYSTEM)                SourceSystem,
        MAX(Activity.TRANSACTION_DATE)            TransactionDate,
        MAX(Activity.EFFECTIVE_DATE)            DateReceived,
        SUM(Activity.AMOUNT) AS                    Amount,
        '' AS                                   SolicitorID,
        '' AS                                   CheckNumber,
        '' AS                                    PaymentType,
        CONVERT(int, SUBSTRING(CONVERT(char(6), MAX(Trans.FISCAL_PERIOD)), 5, 2)) AS FiscalMonth,     
        CONVERT(int,substring(CONVERT(char(6), MAX(Trans.FISCAL_PERIOD)),1,4)) AS FiscalYear,
        'Gift' AS                               GiftType,
        0 AS                                    MatchingTransaction,
        0 AS                                    IsMatchingGift,
        '' AS                                   MemorialID,
        '' AS                                   ListAs,
        0 AS                                    RequestNumber,
        '' AS                                   MemorialNameText     
    FROM Activity
        INNER JOIN Trans ON
            Activity.ORIGINATING_TRANS_NUM = Trans.TRANS_NUMBER
        INNER JOIN TransWatch ON
            TransWatch.TransactionNumber = Activity.ORIGINATING_TRANS_NUM AND
            TransWatch.InvoiceNumber = Trans.INVOICE_REFERENCE_NUM
        INNER JOIN Invoice ON
            Invoice.REFERENCE_NUM = Trans.INVOICE_REFERENCE_NUM
        WHERE
            Activity.ACTIVITY_TYPE = 'GIFT' AND
            Activity.SOURCE_SYSTEM = 'MEETING' AND
            Trans.TRANSACTION_TYPE = 'DIST' AND
            (Trans.PRODUCT_CODE = Activity.PRODUCT_CODE OR Trans.PRODUCT_CODE LIKE Activity.PRODUCT_CODE +'/%')
    GROUP BY Activity.ID, Trans.INVOICE_REFERENCE_NUM, Invoice.ORIGINATING_TRANS_NUM
UNION
    SELECT
        MAX(Activity.ID)                        ID,
        Activity.ORIGINATING_TRANS_NUM  AS        OriginalTransaction,
        0 AS                                    InvoiceRefNum,
        MAX(Activity.SOURCE_SYSTEM)                SourceSystem,
        MAX(Activity.TRANSACTION_DATE)            TransactionDate,
        MAX(Activity.EFFECTIVE_DATE)            DateReceived,
        SUM(Activity.AMOUNT) AS                    Amount,
        '' AS                                   SolicitorID,
        '' AS                                   CheckNumber,
        (CASE MAX(Cash_Accounts.ACCOUNT_TYPE)
            WHEN 1 THEN 'Credit Card'
            WHEN 2 THEN 'In Kind'
            WHEN 3 THEN 'Debit Card'
            ELSE  'Cash' END) AS    PaymentType,
        CONVERT(int, SUBSTRING(CONVERT(char(6), MAX(Trans.FISCAL_PERIOD)), 5, 2)) AS FiscalMonth,     
        CONVERT(int,substring(CONVERT(char(6),MAX(Trans.FISCAL_PERIOD)),1,4)) AS FiscalYear,
        'Gift' AS                               GiftType,
        0 AS                                    MatchingTransaction,
        0 AS                                    IsMatchingGift,
        '' AS                                   MemorialID,
        '' AS                                   ListAs,
        0 AS                                    RequestNumber,
        '' AS                                   MemorialNameText     
    FROM Activity
        INNER JOIN Trans ON
            Activity.ORIGINATING_TRANS_NUM = Trans.TRANS_NUMBER AND
            Trans.ST_ID = Activity.ID AND
            Trans.PRODUCT_CODE = Activity.PRODUCT_CODE
        INNER JOIN TransWatch ON
            TransWatch.TransactionNumber = Trans.TRANS_NUMBER AND
            TransWatch.InvoiceNumber = Trans.INVOICE_REFERENCE_NUM
        LEFT OUTER JOIN Cash_Accounts ON
            Trans.CHECK_NUMBER = Cash_Accounts.CASH_ACCOUNT_CODE
    WHERE
        Activity.ACTIVITY_TYPE = 'GIFT'  AND
        Activity.SOURCE_SYSTEM IN ('DUES' , 'SC') AND
        Trans.TRANSACTION_TYPE = 'DIST'
    GROUP BY Activity.ID, Activity.ORIGINATING_TRANS_NUM
UNION
    SELECT
        ID,
        OriginalTransaction,
        InvoiceRefNum,
        SourceSystem,
        TransactionDate,
        DateReceived,
        Amount,
        SolicitorID,
        CheckNumber,
        PaymentType,
        FiscalMonth,
        FiscalYear,
        GiftType,
        MatchingTransaction,
        IsMatchingGift,
        MemorialID,
        ListAs,
        RequestNumber,
        MemorialNameText
    FROM DonationReport
    WHERE OriginalTransaction NOT IN (SELECT TransWatch.TransactionNumber FROM TransWatch )

GO
GRANT REFERENCES ON  [dbo].[vDonations] TO [IMIS]
GRANT SELECT ON  [dbo].[vDonations] TO [IMIS]
GRANT INSERT ON  [dbo].[vDonations] TO [IMIS]
GRANT DELETE ON  [dbo].[vDonations] TO [IMIS]
GRANT UPDATE ON  [dbo].[vDonations] TO [IMIS]
GO